package dao;

import java.util.Date;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Collections;
import java.util.List;


import TouristManagement.Advice;
import TouristManagement.CheckInNotValid;


public class VisiteDAO {
	
	private MySqlConnecte myConn = new  MySqlConnecte();
	
	//Verifier visite: return true si déjà visite, false si non 
	public boolean verifyVisit(int idSite,int idTouriste){
		try{
			final String queryOrdre = "SELECT COUNT(*) FROM visite WHERE idSite=? and idTouriste=?";
			PreparedStatement preparedQuery = this.myConn.getConnection().prepareStatement(queryOrdre);
			preparedQuery.setInt(1, idSite);
			preparedQuery.setInt(2,idTouriste);
			final ResultSet res = preparedQuery.executeQuery();
			res.first();
			final int compte = res.getInt(1);
			return 	(compte>0);
		}
		catch(Exception e){
			e.printStackTrace(System.err);
			return false;
		}		
	}
	
	//addVisite quand un touriste visite un site
	public boolean addVsite(int idSite, int idTouriste){
		try{
			//getOrdre 
			String query = "SELECT ifnull(max(ordre)+1,1) FROM visite where idTouriste=?";
			PreparedStatement preparedQuery = this.myConn.getConnection().prepareStatement(query);
			preparedQuery.setInt(1, idTouriste);
			final ResultSet res = preparedQuery.executeQuery();
			res.first();
			final int ordre = res.getInt(1);
			if(ordre<=0){
				throw new CheckInNotValid("");
			}
			SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd hh:mm:ss");
			Date now = new Date();
			String strNow = sdf.format(now);
			//addVisite
			query = "INSERT INTO visite(idSite,idTouriste,ordre,dateIn) VALUES(?,?,?,?)";
			preparedQuery = this.myConn.getConnection().prepareStatement(query);
			preparedQuery.setInt(1, idSite);
			preparedQuery.setInt(2, idTouriste);
			preparedQuery.setInt(3, ordre);
			preparedQuery.setString(4,strNow);
					
			return preparedQuery.execute();
			
		}
		catch(Exception e){
			e.printStackTrace(System.err);
			return false;
		}	
	}

	//update heure sortie quand un touriste quitte un site
	public boolean updateVisitEndHour(int idTouriste,int idSite){
		try{
			
			final String queryOrdre = "UPDATE visite SET dateOUT=now() WHERE idSite=? and idTouriste=?";
			PreparedStatement preparedQuery = this.myConn.getConnection().prepareStatement(queryOrdre);
			preparedQuery.setInt(1, idSite);
			preparedQuery.setInt(2,idTouriste);
			return preparedQuery.execute();		
		}
		catch(Exception e){
			e.printStackTrace(System.err);
			return false;
		}		
	}

	//update satisfaction quand un touriste quitte un site
	public boolean addAdvice(int idTouriste,int idSite,Advice adv ){
		try{
			int advInt= adv.value();
			final String queryOrdre = "UPDATE visite SET satisfaction=? WHERE idSite=? and idTouriste=?";
			PreparedStatement preparedQuery = this.myConn.getConnection().prepareStatement(queryOrdre);
			preparedQuery.setInt(1, advInt);
			preparedQuery.setInt(2, idSite);
			preparedQuery.setInt(3,idTouriste);
			return preparedQuery.execute();	
			
		}
		catch(Exception e){
			e.printStackTrace(System.err);
			return false;
		}		
	}
	
	//get niveau satisfaction moyen d'ensemble des sites
	public short getAdviceAverageSite(short idSite){
		try{
			final String queryOrdre = "SELECT avg(satisfaction) FROM visite WHERE idSite=? and satisfaction is not null";
			PreparedStatement preparedQuery = this.myConn.getConnection().prepareStatement(queryOrdre);
			preparedQuery.setInt(1, idSite);
			final ResultSet res = preparedQuery.executeQuery();
			res.first();
			final short compte = res.getShort(1);
			return 	compte;
		
		}
		catch(Exception e){
			e.printStackTrace(System.err);
			return -1;
		}		
	}
	
	//get niveau satisfaction par site
	public short getAdviceAverageSite(int idSite){
		try{
			final String queryOrdre = "SELECT avg(satisfaction) FROM visite WHERE idSite=? AND satisfaction is not null";
			PreparedStatement preparedQuery = this.myConn.getConnection().prepareStatement(queryOrdre);
			preparedQuery.setInt(1, idSite);
			final ResultSet res = preparedQuery.executeQuery();
			res.first();
			final short compte = res.getShort(1);
			return 	compte;
		}
		catch(Exception e){
			e.printStackTrace(System.err);
			return -1;
		}		
	}
		
	//verify if a tourist is visiting a site
	//return idSite is yes , -1 if no
	public short getVisitingSite(int idTouriste){
		try{
			final String queryOrdre = "SELECT idSite FROM visite WHERE idTouriste=? and dateIn is not null and dateOut is null";
			PreparedStatement preparedQuery = this.myConn.getConnection().prepareStatement(queryOrdre);				
			preparedQuery.setInt(1,idTouriste);
			final ResultSet res = preparedQuery.executeQuery();
			res.first();
			final short idSite = res.getShort(1);
			return idSite;			
		}
		catch(Exception e){
			e.printStackTrace(System.err);
			return -1;
		}		
	}
	
	//verify if a tourist is visiting a site
		public boolean isVisitingSite(int idTouriste){
			try{
				final String queryOrdre = "SELECT count(idSite) FROM visite WHERE idTouriste=? and dateIn is not null and dateOut is null";
				PreparedStatement preparedQuery = this.myConn.getConnection().prepareStatement(queryOrdre);				
				preparedQuery.setInt(1,idTouriste);
				final ResultSet res = preparedQuery.executeQuery();
				res.first();
				final short idSite = res.getShort(1);
				return (idSite>0);			
			}
			catch(Exception e){
				e.printStackTrace(System.err);
				return false;
			}		
		}
	

		
	//get Sequence Visite Favoriste d'ensemble des sites
	public String getVisitSequenceFavorite(){
		try{	
			//get nombre de touriste
			String query = "SELECT COUNT(idTouriste) FROM Touriste";
			PreparedStatement preparedQuery = this.myConn.getConnection().prepareStatement(query);
			ResultSet res = preparedQuery.executeQuery();
			res.first();
			int nbTouriste= res.getInt(1);
				
			//get liste de sequence de toutes les touristes
			List<String> listSeq=new ArrayList<String>();//liste sequence visite
			List<Integer> listNbSeq=new ArrayList<Integer>();//nombre de chaque sequence visite
			int ind=0;
			String seq="";
				
			for (int i=0;i<nbTouriste;i++){
				seq="";
				query = "SELECT nomSite FROM site,visite WHERE idTouriste=? and site.idSite=visite.idSite and ordre is not null ORDER BY ordre";
				preparedQuery = this.myConn.getConnection().prepareStatement(query);
				preparedQuery.setInt(1, i+1);
				res = preparedQuery.executeQuery();
				//Contruire la liste des séquence
				while (res.next()){
					seq=seq+res.getString(1)+" - ";
				}
					
				ind=listSeq.indexOf(seq);
				if (!seq.equals("")){
					if (ind==-1){//Si cette sequence n'est pas présenté dans la liste des séquences
						listSeq.add(seq);
						listNbSeq.add(1);
					}
					else //Si cette sequence est présenté dans la liste des séquences
					{
						listNbSeq.set(ind,listNbSeq.get(ind)+1);
					}
				}
			}	
			//get sequence favorite
			int maxS = Collections.max(listNbSeq);
			String str=listSeq.get(listNbSeq.indexOf(maxS));
			return str.substring(0, str.length()-3);
		}			
		catch(Exception e){
			e.printStackTrace(System.err);
			return null;
		}		
	}
	
	//get affluence 
	
	public static void main(String[] args) throws CheckInNotValid {
		
		VisiteDAO t = new VisiteDAO();
		System.out.println(t.isVisitingSite(3));
	}
	
	

}
